package com.products.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import com.products.dao.connection.DataSource;
import com.products.dao.connection.IDataSource;
import com.products.model.Product;

public class Commands {
	private IDataSource dataSource = new DataSource();
	
	public List<Product> getList() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        ArrayList<Product> newsList = new ArrayList<Product>();
        String query = "select * from products;";
        Product product = null;
        try {
            connection = dataSource.getConnection();
            Statement st = connection.createStatement();
            resultSet = st.executeQuery(query);
    		while(resultSet.next()){
    			product = new Product();
    			product.setProductId(Integer.parseInt(resultSet.getString(1)));
    			product.setProductName(resultSet.getString(2));
    			product.setProductAmount(Integer.parseInt(resultSet.getString(3)));
    			newsList.add(product);
    		}
            return newsList;
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
            } catch (SQLException ex) {
            	ex.printStackTrace();
            }
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException ex) {
            	ex.printStackTrace();
            }
            try {
                if (connection != null) {
                	dataSource.freeConnection(connection);
                }
            } catch (Exception ex) {
            	ex.printStackTrace();
            }
        }
        return null;
	}
	
	public String getMail() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        String query = "select * from responsible;";
        try {
            connection = dataSource.getConnection();
            Statement st = connection.createStatement();
            resultSet = st.executeQuery(query);
            if (resultSet.next()){
            	return resultSet.getString(2);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
            } catch (SQLException ex) {
            	ex.printStackTrace();
            }
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException ex) {
            	ex.printStackTrace();
            }
            try {
                if (connection != null) {
                	dataSource.freeConnection(connection);
                }
            } catch (Exception ex) {
            	ex.printStackTrace();
            }
        }
        return null;
	}
	
	public void removeOrder() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        String query = "update products set productAmount = 0;";
        try {
            connection = dataSource.getConnection();
            Statement st = connection.createStatement();
            st.execute(query);
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
            } catch (SQLException ex) {
            	ex.printStackTrace();
            }
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException ex) {
            	ex.printStackTrace();
            }
            try {
                if (connection != null) {
                	dataSource.freeConnection(connection);
                }
            } catch (Exception ex) {
            	ex.printStackTrace();
            }
        }
	}
	
}
